# -*-coding:utf-8 -*-
import requests
from lxml import etree
import pymysql
'虎牙视频总排行榜（功能18）'


# 获取页面数据
def getPageInfo():
    url = 'https://v.huya.com/rank/all.html'
    # 返回一个排行榜数据的数组
    videoArr = []
    response = requests.get(url=url)
    response.encoding = 'utf-8'
    tree = etree.HTML(response.text)
    # 路径
    path = "//div[@class='vhuya-rank-cont']/ul/li"
    list = tree.xpath(path)
    for li in list:
        # up主昵称
        nick = li.xpath("./div/div/div/a/text()")[0].strip()
        count = li.xpath("./div/div/div/span/text()")[0].strip()
        # 将超过一万播放量的视频改为数字类型
        if count[-1] == '万':
            count = count.strip('万')
            floatCount = float(count) * 10000
            viewCount = int(floatCount)
        # 将超过1000的视频改成数字类型
        elif count.find(',') != -1:
            count = count.replace(',', "")
            viewCount = int(count)
        score = li.xpath("./div[2]/text()")[0].strip()
        title = li.xpath("./div/div/h2/a/text()")[0].strip()
        # 记录进比赛信息里面
        videoDict = {
            "nick": nick,
            "viewCount": viewCount,
            "score": score,
            "title": title
        }
        videoArr.append(videoDict)
    return videoArr


# 创建MySQL连接函数（当做固定）
def getMysqlConn():
    conn = pymysql.connect(host='106.13.194.7',
                           port=3306,
                           user='rdsroot',
                           password='abc123456',
                           db='huya',
                           charset='utf8')
    # 创建操作的游标
    cursor = conn.cursor()
    # 设置字符编码及自动提交
    cursor.execute('set names utf8')  # 固定格式
    cursor.execute('set autocommit=1')
    # 设置自动提交
    return conn, cursor


# 排行榜信息
def saveVideoData(videoArr):
    conn, cursor = getMysqlConn()
    # sql语句
    sql = "insert into t_video (id,nick,viewCount,score,title) values(REPLACE(UUID(),'-',''),%s,%s,%s,%s)"
    for video in videoArr:
        add_data = (video["nick"], video["viewCount"], video["score"],
                    video["title"])
        # 这里会把sql语句中的%s替换掉
        cursor.execute(sql, add_data)
    print("排行榜插入数据完成")
    # 关闭连接
    cursor.close()
    conn.close()


if __name__ == '__main__':
    videoArr = getPageInfo()
    saveVideoData(videoArr)
